package com.lab.util;

import java.io.File;
import java.util.Arrays;

import com.lab.io.FileUtils;

public class Excel {

    StringBuffer excel = new StringBuffer();

    private int len;
    private String[] header;
    private String[] data;
    private int[] columnIdx;

    private String xmlVersion = "<?xml version=\"1.0\"?>";
    private String mso = "<?mso-application progid=\"Excel.Sheet\"?>";
    private String workBookHeader = "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:dt=\"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">";
    private String documentProperties = "<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\"><Author>Kevin Nick</Author><LastAuthor>Administrator</LastAuthor><LastPrinted>1899-12-30T00:00:00Z</LastPrinted><Created>2010-12-31T11:40:38Z</Created><LastSaved>1899-12-30T00:00:00Z</LastSaved><Version>11.9999</Version></DocumentProperties>";
    private String customDocumentProperties = "<CustomDocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\"><KSOProductBuildVer dt:dt=\"string\">2052-6.6.0.2461</KSOProductBuildVer></CustomDocumentProperties>";
    private String excelWorkbook = "<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\"><WindowHeight>10200</WindowHeight><WindowWidth>21255</WindowWidth><WindowTopX>0</WindowTopX><WindowTopY>0</WindowTopY><ProtectStructure>False</ProtectStructure><ProtectWindows>False</ProtectWindows></ExcelWorkbook>";
    private String styles = "<Styles><Style ss:ID=\"Default\" ss:Name=\"Normal\"><Alignment ss:Vertical=\"Center\" /><Borders /><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\" /><Interior /><NumberFormat /><Protection /></Style><Style ss:ID=\"s19\" ss:Name=\"宋体_Sheet1\"><Alignment ss:Vertical=\"Bottom\" /><Borders /><Font x:Family=\"Swiss\" /><Interior /><NumberFormat /><Protection /></Style></Styles>";
    private String worksheetHeader = "<Worksheet ss:Name=\"Sheet1\">";
    private String tableHeader = "";
    private String tableFooter = "</Table>";
    private String worksheetOptions = "";
    private String worksheetFooter = "</Worksheet>";
    private String workBookFooter = "</Workbook>";

    private String rowHeader = "<Row>";
    private String cellHeader = "<Cell ss:StyleID=\"s19\">";
    private String dataHeader = "<Data ss:Type=\"String\">";
    private String dataFooter = "</Data>";
    private String cellFooter = "</Cell>";
    private String rowFooter = "</Row>";

    /**
     * 为防止OutOfMemory, 到达最大记录数写入文件.
     * 
     */
    private final static int MAX_COUNT = 10000;

    public Excel() {}

    /**
     * @param header
     *            表头.
     * @param data
     *            记录.
     * @param columnIdx
     *            需要递增的列索引.
     * @param len
     *            记录长度.
     */
    public Excel(String[] header, String[] data, int[] columnIdx, int len) {
        this.header = header;
        this.data = data;
        this.columnIdx = columnIdx;
        this.len = len;

        build();
    }

    private void build() {
        setTableHeader();
        setWorksheetOptions();

        excel.append(xmlVersion).append(mso).append(workBookHeader).append(
                documentProperties).append(customDocumentProperties).append(
                excelWorkbook).append(styles).append(worksheetHeader).append(
                tableHeader).append(generateHeader());
    }

    public void setTableHeader() {
        tableHeader = "<Table ss:ExpandedColumnCount=\"13\" ss:ExpandedRowCount=\""
                + (len + 1)
                + "\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">";
    }

    public void setWorksheetOptions() {
        worksheetOptions = "<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"><PageSetup><Header x:Margin=\"0.51111111111111107\" /><Footer x:Margin=\"0.51111111111111107\" /></PageSetup><Print><ValidPrinterInfo /><PaperSizeIndex>9</PaperSizeIndex><VerticalResolution>0</VerticalResolution></Print><PageBreakZoom>100</PageBreakZoom><Selected /><Panes><Pane><Number>3</Number><ActiveRow>"
                + (len + 1)
                + "</ActiveRow></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions>";
    }

    private StringBuffer generateHeader() {
        StringBuffer row = new StringBuffer(rowHeader);
        for (int idx = 0, len = header.length; idx < len; idx++) {
            row.append(cellHeader).append(dataHeader).append(header[idx])
                    .append(dataFooter).append(cellFooter);
        }
        row.append(rowFooter);

        return row;
    }

    /**
     * 生成文件.
     * 
     * @param file
     *          文件名.
     * @throws Exception
     */
    public void generate(File file) throws Exception {
        if (file.exists()) {
            file.delete();
        }

        int flag = 0;
        for (int idx = 0; idx < len; idx++) {
            ++flag;
            StringBuffer row = new StringBuffer(rowHeader);
            for (int idx2 = 0, len = data.length; idx2 < len; idx2++) {
                if (columnIdxExists(idx2)) {
                    row.append(cellHeader).append(dataHeader)
                            .append(data[idx2]).append(flag).append(dataFooter)
                            .append(cellFooter);
                } else {
                    row.append(cellHeader).append(dataHeader)
                            .append(data[idx2]).append(dataFooter).append(
                                    cellFooter);
                }
            }
            row.append(rowFooter);
            excel.append(row);

            if (idx != 0 && idx % MAX_COUNT == 0) {
                FileUtils.write(file, excel.toString(), true);
                excel = new StringBuffer();
            }
        }

        excel.append(tableFooter).append(worksheetOptions).append(
                worksheetFooter).append(workBookFooter);
        FileUtils.write(file, excel.toString(), true);
    }

    /**
     * 是否为递增的列索引.
     * 
     * @param idx
     *           列索引.
     * @return
     */
    private boolean columnIdxExists(int idx) {
        if (columnIdx == null || columnIdx.length == 0) {
            return false;
        }

        return Arrays.binarySearch(columnIdx, idx) >= 0;
    }

    public static void main(String[] args) throws Exception {
    /* String[] data = { "20101001", "阿斯_", "1", "19890101", "9",
             "test_test_", "快捷键发士大夫士大夫的的士大夫我134 士大夫", "133331133",
             "02121515-4513", "200000", "sdfsff@sd.com", "acc_", "N" };
     String[] header = { "投保日期", "投保人姓名", "性别", "生日", "ID类型", "身份ID", "地址1",
             "手机", "固定电话", "邮编", "邮箱", "帐号（识别码）", "名单性质" };
     int[] columnIdx = { 1, 5, 11 };

     Excel excel = new Excel(header, data, columnIdx, 200000);
     excel.generate(new File("D:\\tmp.xls"));*/

    /*        String[] data = { "cm_test_", "9", "cm_test_", "ewrwerwe3234234234",
                    "13911111111", "11111111", "200000", "sdf@@@2r3", "cm_test_",
                    "R" };
            String[] header = { "投保人姓名", "ID类型", "身份ID", "地址1", "手机", "固定电话", "邮编",
                    "邮箱", "帐号（识别码）", "名单性质" };
            int[] columnIdx = { 0, 2, 8 };

            Excel excel = new Excel(header, data, columnIdx, 30000);
            excel.generate(new File("D:\\tmp.xls"));*/

    }
}
